<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8">
	<title>Database - Query Builder Select - Fuel Documentation</title>
	<link href="../../assets/css/main.css" media="screen" rel="stylesheet" />
	<script type="text/javascript" src="../../assets/js/jquery-1.4.4.min.js"></script>
	<script type="text/javascript" src="../../assets/js/nav.js"></script>
	<script type="text/javascript" src="../../assets/js/highlight.pack.js"></script>
	<script type="text/javascript">
		$(function() {
			show_nav('classes', '../../');
		});
		hljs.tabReplace = '    ';
		hljs.initHighlightingOnLoad();
	</script>
</head>
<body>

	<header>
		<h1>Fuel Documentation</h1>
	</header>

	<div id="main-nav"></div>

	<section id="content">
		<h2 id="introduction">Query_Builder_Select class (extends <a href="qb_where.html">Query_Builder_Where</a></h2>
		
		<p>
			The Query_Builder_Select class handles all the select operations for the query building process. It extends the
			Query_Builder_Where class, so all the methods are inherrited.
		</p>
		
		<h2 id="methods">Methods</h2>

		<article>
			<h4 id="method_distinct">distinct($value = true)</h4>
			<p>The <strong>distinct</strong> method sets whether to select distinct values.</p>
			<table class="method">
				<tbody>
					<tr>
						<th>Static</th>
						<td>No</td>
					</tr>
					<tr>
						<th>Parameters</th>
						<td>
							<table class="parameters">
								<tr>
									<th>Param</th>
									<th>Type</th>
									<th>Default</th>
									<th class="description">Description</th>
								</tr>
								<tr>
									<th><kbd>$value</kbd></th>
									<td><em>bool</em></td>
									<td><pre class="php"><code>true</code></pre></td>
									<td>set to false if you don't want to select distinct values</td>
								</tr>
							</table>
						</td>
					</tr>
					<tr>
						<th>Returns</th>
						<td>Returns the current instance.</td>
					</tr>
					<tr>
						<th>Example</th>
						<td>
							<pre class="php"><code>// prepare a select statement
$query = DB::select('name')->from('users');

// Set it to distinct
$query->distinct();

// SELECT DISTINCT `name` FROM `users`
</code></pre>
						</td>
					</tr>
				</tbody>
			</table>
		</article>
		
		<article>
			<h4 id="method_select">select($columns = null)</h4>
			<p>The <strong>select</strong> method appends columns to select.</p>
			<table class="method">
				<tbody>
					<tr>
						<th>Static</th>
						<td>No</td>
					</tr>
					<tr>
						<th>Parameters</th>
						<td>
							<table class="parameters">
								<tr>
									<th>Param</th>
									<th>Type</th>
									<th>Default</th>
									<th class="description">Description</th>
								</tr>
								<tr>
									<th><kbd>$columns</kbd></th>
									<td><em>mixed</em></td>
									<td><pre class="php"><code>true</code></pre></td>
									<td>column name or array($column, $alias)</td>
								</tr>
							</table>
						</td>
					</tr>
					<tr>
						<th>Returns</th>
						<td>Returns the current instance.</td>
					</tr>
					<tr>
						<th>Example</th>
						<td>
							<pre class="php"><code>// prepare a select statement
$query = DB::select('name')->from('users');

// Append other columns to select
$query->select('surname', 'email');

// Supply an array to use an alias
$query->select(
	array('is_active', 'active'),
	'birthdate'
);

// SELECT `surname`, `email`, `is_active` as active, `birthdate` FROM `users`
</code></pre>
						</td>
					</tr>
				</tbody>
			</table>
		</article>
		
		<article>
			<h4 id="method_select_array">select_array(array $columns)</h4>
			<p>The <strong>select_array</strong> method appends columns to select.</p>
			<table class="method">
				<tbody>
					<tr>
						<th>Static</th>
						<td>No</td>
					</tr>
					<tr>
						<th>Parameters</th>
						<td>
							<table class="parameters">
								<tr>
									<th>Param</th>
									<th>Type</th>
									<th>Default</th>
									<th class="description">Description</th>
								</tr>
								<tr>
									<th><kbd>$columns</kbd></th>
									<td><em>array</em></td>
									<td><em>required</em></td>
									<td>an array containing column names</td>
								</tr>
							</table>
						</td>
					</tr>
					<tr>
						<th>Returns</th>
						<td>Returns the current instance.</td>
					</tr>
					<tr>
						<th>Example</th>
						<td>
							<pre class="php"><code>// prepare a select statement
$query = DB::select('name')->from('users');

// Append other columns to select
$query->select('surname', 'email');

// Supply an array to use an alias
$query->select_array(array(
	array('is_active', 'active'),
	'birthdate'
));

// SELECT `surname`, `email`, `is_active` as active, `birthdate` FROM `users ...
</code></pre>
						</td>
					</tr>
				</tbody>
			</table>
		</article>

		<article>
			<h4 id="method_from">from($tables)</h4>
			<p>The <strong>from</strong> method appends tables to select from.</p>
			<table class="method">
				<tbody>
					<tr>
						<th>Static</th>
						<td>No</td>
					</tr>
					<tr>
						<th>Parameters</th>
						<td>
							<table class="parameters">
								<tr>
									<th>Param</th>
									<th>Type</th>
									<th>Default</th>
									<th class="description">Description</th>
								</tr>
								<tr>
									<th><kbd>$tables</kbd></th>
									<td><em>mixed</em></td>
									<td><em>required</em></td>
									<td>table names or array($table, $alias)</td>
								</tr>
							</table>
						</td>
					</tr>
					<tr>
						<th>Returns</th>
						<td>Returns the current instance.</td>
					</tr>
					<tr>
						<th>Example</th>
						<td>
							<pre class="php"><code>// prepare a select statement
$query = DB::select()->from('users');

// Append tables to select from
$query->from(
	'admins',
	array('comments', 'cmmnts')
);

// SELECT * FROM `users`, `admins`, `comments` cmmnts
</code></pre>
						</td>
					</tr>
				</tbody>
			</table>
		</article>
		
		<article>
			<h4 id="method_join">join($table, $type = null)</h4>
			<p>The <strong>join</strong> method appends tables to join.</p>
			<table class="method">
				<tbody>
					<tr>
						<th>Static</th>
						<td>No</td>
					</tr>
					<tr>
						<th>Parameters</th>
						<td>
							<table class="parameters">
								<tr>
									<th>Param</th>
									<th>Type</th>
									<th>Default</th>
									<th class="description">Description</th>
								</tr>
								<tr>
									<th><kbd>$table</kbd></th>
									<td><em>mixed</em></td>
									<td><em>required</em></td>
									<td>table name or array($table, $alias)</td>
								</tr>
								<tr>
									<th><kbd>$type</kbd></th>
									<td><em>mixed</em></td>
									<td><pre class="php"><code>true</code></pre></td>
									<td>join type (LEFT, RIGHT, INNER, etc)</td>
								</tr>
							</table>
						</td>
					</tr>
					<tr>
						<th>Returns</th>
						<td>Returns the current instance.</td>
					</tr>
					<tr>
						<th>Example</th>
						<td>
							<pre class="php"><code>// prepare a select statement
$query = DB::select()->from('users');

// Join a table
$query->join('profiles');

// SELECT * FROM `users` JOIN `profiles`

// or...

$query = DB::select()->from('users');

// Join another table
$query->join('pictures', 'RIGHT OUTER');

// SELECT * FROM `users` RIGHT OUTER JOIN `pictures`
</code></pre>
						</td>
					</tr>
				</tbody>
			</table>
		</article>
		
		<article>
			<h4 id="method_on">on($c1, $op, $c2)</h4>
			<p>The <strong>on</strong> method adds "ON ..." conditions for the last created JOIN statement.</p>
			<table class="method">
				<tbody>
					<tr>
						<th>Static</th>
						<td>No</td>
					</tr>
					<tr>
						<th>Parameters</th>
						<td>
							<table class="parameters">
								<tr>
									<th>Param</th>
									<th>Type</th>
									<th>Default</th>
									<th class="description">Description</th>
								</tr>
								<tr>
									<th><kbd>$c1</kbd></th>
									<td><em>mixed</em></td>
									<td><em>required</em></td>
									<td>table name or array($table, $alias)</td>
								</tr>
								<tr>
									<th><kbd>$op</kbd></th>
									<td><em>string</em></td>
									<td><em>required</em></td>
									<td>logical operator</td>
								</tr>
								<tr>
									<th><kbd>$c2</kbd></th>
									<td><em>mixed</em></td>
									<td><em>required</em></td>
									<td>table name or array($table, $alias)</td>
								</tr>
							</table>
						</td>
					</tr>
					<tr>
						<th>Returns</th>
						<td>Returns the current instance.</td>
					</tr>
					<tr>
						<th>Example</th>
						<td>
							<pre class="php"><code>// prepare a select statement
$query = DB::select()->from('users');

// Join a table
$query->join('profiles');
$query->on('users.id', '=', 'profiles.user_id');

// SELECT * FROM `users` JOIN `profiles` ON `users`.`id` = `profiles`.`user_id`
</code></pre>
						</td>
					</tr>
				</tbody>
			</table>
		</article>
		
		<article>
			<h4 id="method_group_by">group_by($columns)</h4>
			<p>The <strong>group_by</strong> method creates a "GROUP BY ..." filter.</p>
			<table class="method">
				<tbody>
					<tr>
						<th>Static</th>
						<td>No</td>
					</tr>
					<tr>
						<th>Parameters</th>
						<td>
							<table class="parameters">
								<tr>
									<th>Param</th>
									<th>Type</th>
									<th>Default</th>
									<th class="description">Description</th>
								</tr>
								<tr>
									<th><kbd>$columns</kbd></th>
									<td><em>mixed</em></td>
									<td><em>required</em></td>
									<td>table name</td>
								</tr>
							</table>
						</td>
					</tr>
					<tr>
						<th>Returns</th>
						<td>Returns the current instance.</td>
					</tr>
					<tr>
						<th>Example</th>
						<td>
							<pre class="php"><code>// prepare a select statement
$query = DB::select()->from('articles');

// Group by 'genre'
$query->group_by('genre', 'category');

// SELECT * FROM `articles` GROUP BY `genre`, `category`
</code></pre>
						</td>
					</tr>
				</tbody>
			</table>
		</article>
		
		<article>
			<h4 id="method_having">having($column, $op, $value = NULL)</h4>
			<p>The <strong>having</strong> method is an alias for <a href="#method_and_having">and_having</a>.</p>
		</article>
		
		<article>
			<h4 id="method_and_having">and_having($column, $op, $value = NULL)</h4>
			<p>The <strong>and_having</strong> method appends a "AND HAVING" statement.</p>
			<table class="method">
				<tbody>
					<tr>
						<th>Static</th>
						<td>No</td>
					</tr>
					<tr>
						<th>Parameters</th>
						<td>
							<table class="parameters">
								<tr>
									<th>Param</th>
									<th>Type</th>
									<th>Default</th>
									<th class="description">Description</th>
								</tr>
								<tr>
									<th><kbd>$column</kbd></th>
									<td><em>string</em></td>
									<td><em>required</em></td>
									<td>Column name or array($column, $alias) or object</td>
								</tr>
								<tr>
									<th><kbd>$op</kbd></th>
									<td><em>string</em></td>
									<td><pre class="php"><code>null</code></pre></td>
									<td>Logic operator: =, !=, IN, BETWEEN and LIKE.</td>
								</tr>
								<tr>
									<th><kbd>$value</kbd></th>
									<td><em>string</em></td>
									<td><pre class="php"><code>null</code></pre></td>
									<td>Column value</td>
								</tr>
							</table>
						</td>
					</tr>
					<tr>
						<th>Returns</th>
						<td>Returns the current instance.</td>
					</tr>
					<tr>
						<th>Example</th>
						<td>
							<pre class="php"><code>// prepare a select statement
$query = DB::select('*')->from('users');

// Set the first HAVING statement
$query->having('name', '!=', 'John');

// and append a new condition
$query->and_having('surname', '=', 'Doe');

// Resulting in: SELECT * FROM `users` HAVING `name` != "John" AND `surname` = "Doe"
</code></pre>
						</td>
					</tr>
				</tbody>
			</table>
		</article>
		
		<article>
			<h4 id="method_having_open">having_open()</h4>
			<p>The <strong>having_open</strong> method is an alias for <a href="#method_and_having_open">and_having_open</a>.</p>
		</article>
		
		<article>
			<h4 id="method_and_having_open">and_having_open()</h4>
			<p>The <strong>and_having_open</strong> method opens an SQL closure and appends it using AND.</p>
			<table class="method">
				<tbody>
					<tr>
						<th>Static</th>
						<td>No</td>
					</tr>
					<tr>
						<th>Parameters</th>
						<td>
							<em>None</em>
						</td>
					</tr>
					<tr>
						<th>Returns</th>
						<td>Returns the current instance.</td>
					</tr>
					<tr>
						<th>Example</th>
						<td>
							<pre class="php"><code>// prepare a select statement
$query = DB::select('*')->from('users');

// Set a HAVING statement
$query->having('name', '=', 'john')

// Open a closure
$query->and_having_open();

// SELECT * FROM `users` HAVING `name` = "John" AND (
</code></pre>
						</td>
					</tr>
				</tbody>
			</table>
		</article>
		
		<article>
			<h4 id="method_or_having_open">or_having_open()</h4>
			<p>The <strong>or_having_open</strong> method opens an SQL closure and appends it using OR.</p>
			<table class="method">
				<tbody>
					<tr>
						<th>Static</th>
						<td>No</td>
					</tr>
					<tr>
						<th>Parameters</th>
						<td>
							<em>None</em>
						</td>
					</tr>
					<tr>
						<th>Returns</th>
						<td>Returns the current instance.</td>
					</tr>
					<tr>
						<th>Example</th>
						<td>
							<pre class="php"><code>// prepare a select statement
$query = DB::select('*')->from('users');

// Set a HAVING statement
$query->having('name', '=', 'john')

// Open a closure
$query->or_having_open();

// SELECT * FROM `users` HAVING `name` = "John" OR (
</code></pre>
						</td>
					</tr>
				</tbody>
			</table>
		</article>
		
		<article>
			<h4 id="method_having_close">having_close()</h4>
			<p>The <strong>having_close</strong> method is an alias for <a href="#method_and_having_close">and_having_close</a>.</p>
		</article>

		<article>
			<h4 id="method_and_having_close">and_having_close()</h4>
			<p>The <strong>and_having_close</strong> method closes an SQL closure.</p>
			<table class="method">
				<tbody>
					<tr>
						<th>Static</th>
						<td>No</td>
					</tr>
					<tr>
						<th>Parameters</th>
						<td>
							<em>None</em>
						</td>
					</tr>
					<tr>
						<th>Returns</th>
						<td>Returns the current instance.</td>
					</tr>
					<tr>
						<th>Example</th>
						<td>
							<pre class="php"><code>// prepare a select statement
$query = DB::select('*')->from('users');

// Open a closure
$query->and_having_open();

// Set a HAVING statement
$query->having('name', '=', 'john');

$query->and_having_close();
// SELECT * FROM `users` HAVING (`name` = "John")
</code></pre>
						</td>
					</tr>
				</tbody>
			</table>
		</article>
		
		<article>
			<h4 id="method_and_having_close">and_having_close()</h4>
			<p>The <strong>and_having_close</strong> method closes an SQL closure.</p>
			<table class="method">
				<tbody>
					<tr>
						<th>Static</th>
						<td>No</td>
					</tr>
					<tr>
						<th>Parameters</th>
						<td>
							<em>None</em>
						</td>
					</tr>
					<tr>
						<th>Returns</th>
						<td>Returns the current instance.</td>
					</tr>
					<tr>
						<th>Example</th>
						<td>
							<pre class="php"><code>// prepare a select statement
$query = DB::select('*')->from('users');

// Set a HAVING statement
$query->having('email', 'like', '%@example.com');

// Open a closure
$query->and_having_open();

// Set a HAVING statement
$query->having('name', '=', 'John');
$query->or_where('surname', 'Doe');

$query->and_having_close();
// SELECT * FROM `users` HAVING `email` LIKE "%example.com" AND (`name` = "John" OR `surname` = "Doe")
</code></pre>
						</td>
					</tr>
				</tbody>
			</table>
		</article>
		
		<article>
			<h4 id="method_or_having_close">or_having_close()</h4>
			<p>The <strong>or_having_close</strong> method closes an SQL closure.</p>
			<table class="method">
				<tbody>
					<tr>
						<th>Static</th>
						<td>No</td>
					</tr>
					<tr>
						<th>Parameters</th>
						<td>
							<em>None</em>
						</td>
					</tr>
					<tr>
						<th>Returns</th>
						<td>Returns the current instance.</td>
					</tr>
					<tr>
						<th>Example</th>
						<td>
							<pre class="php"><code>// prepare a select statement
$query = DB::select('*')->from('users');

// Set a HAVING statement
$query->having('email', 'like', '%@example.com');

// Open a closure
$query->or_having_open();

// Set a HAVING statement
$query->having('name', '=' 'John');
$query->and_having('surname', '=', 'Doe');

$query->or_having_close();
// SELECT * FROM `users` HAVING `email` LIKE "%example.com" OR (`name` = "John" AND `surname` = "Doe")
</code></pre>
						</td>
					</tr>
				</tbody>
			</table>
		</article>
		
		<article>
			<h4 id="method_offset">offset($number)</h4>
			<p>The <strong>offset</strong> method sets row number to start from when selecting/updating/deleting.</p>
			<table class="method">
				<tbody>
					<tr>
						<th>Static</th>
						<td>No</td>
					</tr>
					<tr>
						<th>Parameters</th>
						<td>
							<table class="parameters">
								<tr>
									<th>Param</th>
									<th>Type</th>
									<th>Default</th>
									<th class="description">Description</th>
								</tr>
								<tr>
									<th><kbd>$number</kbd></th>
									<td><em>int</em></td>
									<td><em>required</em></td>
									<td>The row number to start from</td>
								</tr>
							</table>
						</td>
					</tr>
					<tr>
						<th>Returns</th>
						<td>Returns the current instance</td>
					</tr>
					<tr>
						<th>Example</th>
						<td>
							<pre class="php"><code>// prepare a select statement
$query = DB::select('*')->from('users');

// Set the limit
$query->limit(10);

$query->offset(5);

// SELECT * FROM `users` LIMIT 10 OFFSET 5
</code></pre>
						</td>
					</tr>
				</tbody>
			</table>
		</article>
		
		<article>
			<h4 id="method_compile">compile(\Database_Connection$db)</h4>
			<p>The <strong>compile</strong> method returns the select SQL query as a string.</p>
			<table class="method">
				<tbody>
					<tr>
						<th>Static</th>
						<td>No</td>
					</tr>
					<tr>
						<th>Parameters</th>
						<td>
							<table class="parameters">
								<tr>
									<th>Param</th>
									<th>Type</th>
									<th>Default</th>
									<th class="description">Description</th>
								</tr>
								<tr>
									<th><kbd>$db</kbd></th>
									<td><em>object</em></td>
									<td><em>required</em></td>
									<td>A database connection</td>
								</tr>
							</table>
						</td>
					</tr>
					<tr>
						<th>Returns</th>
						<td>Returns the SQL query as a string.</td>
					</tr>
					<tr>
						<th>Example</th>
						<td>
							<pre class="php"><code>// prepare a select statement
$query = DB::select('*')->from('users');

// Set the limit and offset
$query->limit(10);
$query->offset(5);

// Get the database connection
$connection = Database_Connection::instance();

// Get the sql query
$sql = $query->compile($connection);
</code></pre>
						</td>
					</tr>
				</tbody>
			</table>
		</article>
		
		<article>
			<h4 id="method_reset">reset()</h4>
			<p>The <strong>reset</strong> method resets all values of the current instance.</p>
			<table class="method">
				<tbody>
					<tr>
						<th>Static</th>
						<td>No</td>
					</tr>
					<tr>
						<th>Parameters</th>
						<td>
							<em>None</em>
						</td>
					</tr>
					<tr>
						<th>Returns</th>
						<td>Returns the current instance.</td>
					</tr>
					<tr>
						<th>Example</th>
						<td>
							<pre class="php"><code>// prepare a select statement
$query = DB::select('*')->from('users');

// Set some conditions
$query->where('name', 'bob');
$query->where('surname', 'unknown');

// Reset it
$query->reset();

// Set the new values
$query->select('email')->from('admins')->where('role', 'superadmin');

// The SQL query will now be:
// SELECT `email` FROM `admins` WHERE `role` = "superadmins"
</code></pre>
						</td>
					</tr>
				</tbody>
			</table>
		</article>
				
	</section>

	<section id="footer">
		<p>
			<a href="http://fuelphp.com">Fuel</a> is released under the MIT license.<br />
			&copy; 2010 - 2011 Fuel Development Team
		</p>
	</section>

</body>
</html>
